package ylsb.controller;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import ylsb.model.*;
import ylsb.service.*;
import ylsb.support.JsonResult;
import ylsb.support.StringHandle;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.lang.Boolean;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("yiyongjieguo")
public class YiyongijieguoController {

    @Value("${userfilepath}")
    private String userfilepath;
    @Resource
    YiyongjieguoService ddService;
    @Resource
    YiyonghaocaiService ddYiyonghaocaiService;
    @Resource
    YltprenyuanfenpeiService ddYltprenyuanfenpeiService;
    @Resource
    YltpXiangmuService ddYltpXiangmuService;
    @Resource
    YiyongyuanzeService ddYiyongyuanzeService;

    public Logger log = LoggerFactory.getLogger(YiyongijieguoController.class);

    @RequestMapping("toupiao")
    public JsonResult<?> toupiao(@RequestParam("id") String ppdetailId,
                                 @RequestParam("pinzhong") int ppPinzhong,
                                 @RequestParam("pinpai") String ppPinpai,
                                 @RequestParam("userid") String ppuserId,
                                 @RequestParam("result") String ppresult){

        try {
            Yiyongjieguo mmjieguo=new Yiyongjieguo();
            Yiyonghaocai mmYihonghaocai=ddYiyonghaocaiService.findOne(ppdetailId);

            YltpXiangmu mmxiangmu=ddYltpXiangmuService.findOne(mmYihonghaocai.getXiangmuid());
            if(mmxiangmu.getIsactived()!=1){
                return JsonResult.getErrorResult("表决失败，投票尚未启动");
            }

            List<YltpRenyuanfenpei> listfenpei = ddYltprenyuanfenpeiService.findbyUserIDandXiangmuId(ppuserId,mmYihonghaocai.getXiangmuid());
            if(listfenpei.get(0).getIssubmit()==1){
                return JsonResult.getErrorResult("表决失败，当前投票项已提交");
            }
            /*if(ddYiyongyuanzeService.checkexistByUserId(ppPinzhong, ppPinpai,ppuserId,mmYihonghaocai.getXiangmuid())==null){
                return JsonResult.getErrorResult("表决失败，请先选择适用原则");
            }*/
            if(ppresult.equals("1")) {
                if (ddService.checkCountByPinzhongandUserId(ppPinzhong, ppuserId,mmYihonghaocai.getXiangmuid()) < 3 ) {
                    if (ddService.checkexistByUserId(ppPinzhong, ppPinpai, ppuserId,mmYihonghaocai.getXiangmuid()) != null) {
                        String mmId = ddService.checkexistByUserId(ppPinzhong, ppPinpai, ppuserId,mmYihonghaocai.getXiangmuid()).getId();
                        mmjieguo.setId(mmId);
                        mmjieguo.setXinghao(mmYihonghaocai.getXiangmuid());
                        mmjieguo.setResult(ppresult);
                        mmjieguo.setCreatetime(new Date());
                        return ddService.modify(mmjieguo) == true ? JsonResult.getSuccessResult("表决成功")
                                : JsonResult.getErrorResult("表决失败");
                    }else {
                        mmjieguo.setId(UUID.randomUUID().toString());
                        mmjieguo.setDetailid(ppdetailId);
                        mmjieguo.setPinzhong(ppPinzhong);
                        mmjieguo.setPinpai(ppPinpai);
                        mmjieguo.setUserid(ppuserId);
                        mmjieguo.setXinghao(mmYihonghaocai.getXiangmuid());
                        mmjieguo.setResult(ppresult);
                        mmjieguo.setCreatetime(new Date());
                        return ddService.add(mmjieguo) == true ? JsonResult.getSuccessResult("表决成功")
                                : JsonResult.getErrorResult("表决失败");
                    }
                } else {
                    return JsonResult.getErrorResult("同一品种的品牌同意数量不能超过3条。");
                }
            }else{
                /*if (ddYiyongyuanzeService.checkexistByUserId(ppPinzhong, ppPinpai, ppuserId,mmYihonghaocai.getXiangmuid()) != null) {
                    String mmyzId = ddYiyongyuanzeService.checkexistByUserId(ppPinzhong, ppPinpai, ppuserId,mmYihonghaocai.getXiangmuid()).getId();
                    ddYiyongyuanzeService.delete(mmyzId);
                }*/

                if (ddService.checkexistByUserId(ppPinzhong, ppPinpai, ppuserId,mmYihonghaocai.getXiangmuid()) != null) {
                    String mmId = ddService.checkexistByUserId(ppPinzhong, ppPinpai, ppuserId,mmYihonghaocai.getXiangmuid()).getId();
                    mmjieguo.setId(mmId);
                    mmjieguo.setXinghao(mmYihonghaocai.getXiangmuid());
                    mmjieguo.setResult(ppresult);
                    mmjieguo.setCreatetime(new Date());
                    return ddService.modify(mmjieguo) == true ? JsonResult.getSuccessResult("表决成功")
                            : JsonResult.getErrorResult("表决失败");
                }else {
                    mmjieguo.setId(UUID.randomUUID().toString());
                    mmjieguo.setDetailid(ppdetailId);
                    mmjieguo.setPinzhong(ppPinzhong);
                    mmjieguo.setPinpai(ppPinpai);
                    mmjieguo.setUserid(ppuserId);
                    mmjieguo.setXinghao(mmYihonghaocai.getXiangmuid());
                    mmjieguo.setResult(ppresult);
                    mmjieguo.setCreatetime(new Date());
                    return ddService.add(mmjieguo) == true ? JsonResult.getSuccessResult("表决成功")
                            : JsonResult.getErrorResult("表决失败");
                }

            }

        }catch (Exception e) {
            e.printStackTrace();
            log.error("yiyongjieguo/toupiao:error",e);
            return JsonResult.getErrorResult("yiyongjieguo/toupiao:error " + e.getMessage());
        }
    }

    @RequestMapping("getResultCount")
    public JsonResult<?> getResultCount(@RequestParam("xiangmuid") String ppxiangmuId,
                                                     @RequestParam("userid") String ppuserId){
        try {
            Integer mmTongyishu=ddService.getResultCount(ppxiangmuId,ppuserId,"1");
            Integer mmTotal=ddYiyonghaocaiService.getVoteCount(ppxiangmuId);
            Map<String, Object> mmMap = new HashMap<String, Object>();
            mmMap.put("tongyiShu", mmTongyishu);
            mmMap.put("total", mmTotal);
            return JsonResult.getSuccessResult(mmMap);
        }catch (Exception e) {
            e.printStackTrace();
            log.error("yiyongjieguo/getResultCount:error",e);
            return JsonResult.getErrorResult("yiyongjieguo/getResultCount:error " + e.getMessage());
        }
    }

    @RequestMapping("votesure")
    public JsonResult<?> votesure(@RequestParam("xiangmuid") String ppxiangmuid,
                                  @RequestParam("userid") String ppuserId){

        try {

            YltpXiangmu mmYsxiangmu=ddYltpXiangmuService.findOne(ppxiangmuid);
            if(mmYsxiangmu.getIsactived()!=1){
                return JsonResult.getErrorResult("表决失败，投票尚未启动");
            }
            List<YltpRenyuanfenpei> mmfenpei=ddYltprenyuanfenpeiService.findbyUserIDandXiangmuId(ppuserId,ppxiangmuid);
            if(mmfenpei.get(0).getIssubmit()==1){
                return JsonResult.getErrorResult("表决失败，投票已提交。");
            }

            List<Map<String, Object>> mmList=ddYiyonghaocaiService.findDaochuPinzhongAll(ppxiangmuid);
            for(Map<String, Object> mmMap:mmList){
                String mmPinzhonxuhao=mmMap.get("pinzhongxuhao").toString();
                List<Map<String,Object>> mmPinpailist = ddYiyonghaocaiService.findPinpaiFenlei(ppxiangmuid,mmPinzhonxuhao);
                if(3>=mmPinpailist.size()){
                    for(Map<String, Object> mmPinpaiMap:mmPinpailist){
                        String mmPinpaixuhao=mmPinpaiMap.get("pinpaixuhao").toString();
                        List<Map<String, Object>> mmjieguoList=ddService.findPinpaiJieguo(ppxiangmuid,ppuserId,mmPinzhonxuhao,mmPinpaixuhao);
                        List<Map<String, Object>> mmdetailList=ddYiyonghaocaiService.findXinghaoByPinpai(ppxiangmuid,mmPinzhonxuhao,mmPinpaixuhao);
                        String mmDetailId=mmdetailList.get(0).get("id").toString();
                        if(mmjieguoList.size()>0){
                            String mmId=mmjieguoList.get(0).get("id").toString();
                            Yiyongjieguo mmjieguo=ddService.findOne(mmId);
                            mmjieguo.setXinghao(ppxiangmuid);
                            mmjieguo.setResult("1");
                            ddService.modify(mmjieguo);

                        }else {
                            Yiyongjieguo mmjieguo=new Yiyongjieguo();
                            String mmId=UUID.randomUUID().toString();
                            mmjieguo.setId(mmId);
                            mmjieguo.setDetailid(mmDetailId);
                            mmjieguo.setUserid(ppuserId);
                            mmjieguo.setXinghao(ppxiangmuid);
                            mmjieguo.setResult("1");
                            mmjieguo.setCreatetime(new Date());
                            mmjieguo.setPinzhong(Integer.parseInt(mmPinzhonxuhao));
                            mmjieguo.setPinpai(mmPinpaixuhao);
                            ddService.add(mmjieguo);
                        }
                    }
                }

            }

            return  JsonResult.getSuccessResult("表决成功");

        }catch (Exception e) {
            e.printStackTrace();
            log.error("yiyongjieguo/votesure:error",e);
            return JsonResult.getErrorResult("yiyongjieguo/votesure:error " + e.getMessage());
        }
    }

    @RequestMapping("submit")
    public JsonResult<?> submit(
            @RequestParam("xiangmuid") String ppxiangmuId,
            @RequestParam("userid") String ppuserId){
        try{
            List<YltpRenyuanfenpei>  list=ddYltprenyuanfenpeiService.findbyUserIDandXiangmuId(ppuserId,ppxiangmuId);
            if(list.size()<1){
                return JsonResult.getErrorResult("提交失败，人员-项目分配信息不存在");
            }

            Boolean isQuefenlei=false;
            Boolean isGuochan=false;
            String mmFenleiErr="";
            String mmGuochanErr="";
            List<Map<String, Object>> mmPinzhongList=ddYiyonghaocaiService.findDaochuPinzhongAll(ppxiangmuId);
            for(Map<String, Object> mmMap:mmPinzhongList){
                String mmPinzhonxuhao=mmMap.get("pinzhongxuhao").toString();

                List<Map<String, Object>> mmResiltList=ddService.findResultBypinzhong(ppxiangmuId,ppuserId,mmPinzhonxuhao);
                if(mmResiltList.size()==0){
                    isQuefenlei=true;
                    mmFenleiErr=mmFenleiErr+","+"【"+mmPinzhonxuhao+"】";
                }
                List<Map<String, Object>> mmGuochanList=ddYiyonghaocaiService.findGuochanBypinzhong(ppxiangmuId,mmPinzhonxuhao);
                if(mmGuochanList.size()!=0){
                    List<Map<String, Object>> mmGuochanResultList=ddYiyonghaocaiService.findResultGuochanBypinzhong(ppxiangmuId,ppuserId,mmPinzhonxuhao);
                    if(mmGuochanResultList.size()==0){
                        isGuochan=true;
                        mmGuochanErr=mmGuochanErr+","+"【"+mmPinzhonxuhao+"】";
                    }
                }

            }
            if(isQuefenlei){
                return JsonResult.getErrorResult("提交失败,品种序号"+mmFenleiErr+"没有勾选。");
            }
            if(isGuochan){
                return JsonResult.getErrorResult("提交失败,品种序号"+mmGuochanErr+"没有国产。");
            }


            YltpRenyuanfenpei mmRenyuanfenpei=new YltpRenyuanfenpei();
            mmRenyuanfenpei.setId(list.get(0).getId());
            mmRenyuanfenpei.setUserid(ppuserId);
            mmRenyuanfenpei.setXiangmuid(ppxiangmuId);
            mmRenyuanfenpei.setIssubmit(1);
            return ddYltprenyuanfenpeiService.update(mmRenyuanfenpei)==true? JsonResult.getSuccessResult("提交成功")
                    : JsonResult.getErrorResult("提交失败");
        }catch (Exception e) {
            e.printStackTrace();
            log.error("renyuanfenpei/submit:error",e);
            return JsonResult.getErrorResult("renyuanfenpei/submit:error " + e.getMessage());
        }

    }
    // 导出某个人员的签字表
    @RequestMapping("qianzibiao")
    public void qianzibiao(HttpServletRequest request, HttpServletResponse response,
                           @RequestParam("xiangmuid") String ppxiangmuid,
                           @RequestParam("xiangmuleixing") String ppxiangmuleixing,
                           @RequestParam("userid") String ppuserid) {
        try {
            ddService.daochuqianzibiao(ppxiangmuid, ppuserid, request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @RequestMapping("findHuizongbiaolist")
    public JsonResult<?> findHuizongbiaolist(@RequestParam("xiangmuid") String ppxiangmuid){
        try {
            List<Map<String,Object>> mmHuizongbiaoList = new ArrayList<>();
            List<Map<String, Object>> mmUserList=ddYltprenyuanfenpeiService.findRenyuanFenpeiByXiangmuId(ppxiangmuid);
            Double mm=((double)2/(double)3)*mmUserList.size();
            int mmTongguopiaoshu=(int)Math.ceil(mm);

            List<Map<String,Object>> mmPinzhongpaixuList=ddYiyonghaocaiService.findDaochuPinzhongAll(ppxiangmuid);
            int a=0;
            for (Map<String, Object> mmPinzhongpaixuMap : mmPinzhongpaixuList) {
                a=a+1;
                String mmPinzhongOne = mmPinzhongpaixuMap.get("pinzhongxuhao").toString();
               /* int mmPinzhongpiaoshu = 0;
                if (mmPinzhongpaixuMap.get("piaoshu") != null && !"".equals(mmPinzhongpaixuMap.get("piaoshu").toString())) {
                    mmPinzhongpiaoshu = Integer.valueOf(mmPinzhongpaixuMap.get("piaoshu").toString());
                }*/

                int mmPinzhongHebingshu = 0;
               /* List<Map<String, Object>> mmPinguiList = ddService.getAgreedPinguiListByPinzhong(ppxiangmuid, mmPinzhong);
                if (mmPinguiList.size()>0){
                    mmPinzhongHebingshu = mmPinguiList.size();
                }else{
                    mmPinzhongHebingshu = mmPinzhongHebingshu+1;
                }*/
                List<Map<String,Object>> mmPinpailist = ddYiyonghaocaiService.findPinpaiByPinzhong(ppxiangmuid,mmPinzhongOne);
                if (mmPinpailist.size()>0){
                    mmPinzhongHebingshu = mmPinpailist.size();
                }else{
                    mmPinzhongHebingshu = mmPinzhongHebingshu+1;
                }
                List<Map<String,Object>> mmPinpaiFenlei = ddService.getPinpaipaixuByPinzhong(ppxiangmuid,mmPinzhongOne);

                for (Map<String, Object> mmPinguiMap : mmPinpaiFenlei) {

                    int mmPinpaiHebingshu = 0;
                    String mmPinpaiOne = mmPinguiMap.get("pinpaixuhao") == null ? "" : mmPinguiMap.get("pinpaixuhao").toString();
                    List<Map<String,Object>> mmXinhaoList = ddYiyonghaocaiService.findXinghaoByPinpai(ppxiangmuid,mmPinzhongOne,mmPinpaiOne);
                        if (mmXinhaoList.size()>0){
                            mmPinpaiHebingshu = mmXinhaoList.size();
                        }else{
                            mmPinpaiHebingshu = mmPinpaiHebingshu+1;
                        }
                    mmPinguiMap.put("xinghaolist",mmXinhaoList);
                    mmPinguiMap.put("pinpaihebinghang",mmPinpaiHebingshu);

                    double mmNumber=Math.ceil((double) mmUserList.size()*((double)2/(double)3));
                    int mmPassNumber=(int)mmNumber;
                    List<Map<String,Object>> mmJieGuoList=ddService.findByxiangmuId(ppxiangmuid);
                    List<Map<String, Object>> mmAgreeList=mmJieGuoList.stream()
                            .filter(jieguo -> jieguo.get("xinghao").toString()
                                    .equals(ppxiangmuid) &&
                                    jieguo.get("pinzhong").toString()
                                            .equals(mmPinzhongOne)&&
                                    jieguo.get("pinpai").toString().equals(mmPinpaiOne)&&
                                    jieguo.get("result").toString().equals("1"))
                            .collect(Collectors.toList());
                    String mmPiaoShu="";
                    String mmyijian="";
                    if(mmAgreeList==null){
                        mmPiaoShu="0／"+String.valueOf(mmUserList.size());
                        mmyijian="不通过";
                    }else {
                        mmPiaoShu=String.valueOf(mmAgreeList.size())+"／"+String.valueOf(mmUserList.size());
                        if(mmAgreeList.size()>=mmPassNumber){
                            mmyijian="通过";
                        }else{
                            mmyijian="不通过";
                        }
                    }

                   /* int mmPinguipiaoshu = 0;
                    if(mmPinguiMap.get("piaoshu")==null){
                        mmPinguipiaoshu=0;
                    }else {
                        mmPinguipiaoshu=Integer.valueOf(mmPinguiMap.get("piaoshu").toString());
                    }
                    String mmPinzhongtext = String.valueOf(mmPinzhongpiaoshu) + "/" + String.valueOf(mmUserList.size());
                    String mmPinguitext = String.valueOf(mmPinguipiaoshu) + "/" + String.valueOf(mmUserList.size());
                    mmPinguiMap.put("pinzhongtext",mmPinzhongtext);*/
                    mmPinguiMap.put("pinguitext",mmPiaoShu);

                    /*String mmJieguotext="";

                    if(mmPinguipiaoshu>=mmTongguopiaoshu){
                        mmJieguotext="通过";
                    }else {
                        mmJieguotext="不通过";
                    }*/

                    mmPinguiMap.put("jieguotext",mmyijian);

                }
                mmPinzhongpaixuMap.put("pinguilist",mmPinpaiFenlei);
                mmPinzhongpaixuMap.put("pinzhonghebinghang",mmPinzhongHebingshu);

                mmHuizongbiaoList.add(mmPinzhongpaixuMap);
            }
            Map<String, Object> mmReturnMap = new HashMap<String, Object>();
            mmReturnMap.put("HuizongbiaoList", mmHuizongbiaoList);

            return JsonResult.getSuccessResult(mmReturnMap);

        }catch (Exception e) {
            e.printStackTrace();
            log.error("jieguo/findHuizongbiaolist:error",e);
            return JsonResult.getErrorResult("jieguo/findHuizongbiaolist:error " + e.getMessage());
        }
    }
    @RequestMapping("huizongbiao")
    public void huizongbiao(HttpServletRequest request, HttpServletResponse response,
                            @RequestParam("xiangmuid") String ppxiangmuid,
                            @RequestParam("xiangmuleixing") String ppxiangmuleixing){

        response.reset();
        response.setContentType("application/x-download;charset=utf-8");
        List<Map<String,Object>> mmList = ddYiyonghaocaiService.findDaochuPinzhongAll(ppxiangmuid);
        List<Map<String,Object>> mmJieGuoList=ddService.findByxiangmuId(ppxiangmuid);
        List<Map<String, Object>> mmUserList=ddYltprenyuanfenpeiService.findRenyuanFenpeiByXiangmuId(ppxiangmuid);

        double mmNumber=(double) mmUserList.size()*((double)2/(double)3);
       /* double mmNumber=Math.ceil((double) mmUserList.size()*((double)2/(double)3));*/
        int mmPassNumber=(int)mmNumber;
        System.out.println();
        YltpXiangmu mmxiangmu=ddYltpXiangmuService.findOne(ppxiangmuid);
        String mmxiangmuname=mmxiangmu.getXiangmuname();

        OutputStream os=null;
        WritableWorkbook wbook=null;

        try{
            os = new BufferedOutputStream(response.getOutputStream());//得到输出流的语句
            //response.getOutputStream();// 取得输出流
            response.reset();// 清空输出流
            String wenjiantitle = mmxiangmuname+ "汇总表"; //文件名
            String tmptitle ="医用耗材（试剂）遴选专业组预选意见-"+ mmxiangmuname; // 标题
            response.setHeader("Content-disposition", "attachment; filename="+new String(wenjiantitle.getBytes(),"iso8859-1")+".xls");// 设定输出文件头
            response.setContentType("application/vnd.ms-excel");// 定义输出类型
            wbook = Workbook.createWorkbook(os); // 建立excel文件
            WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称
            // 设置excel标题
            WritableFont wfont = new WritableFont(WritableFont.ARIAL, 14,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); //设置表格样式

            WritableCellFormat wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);

            WritableCellFormat wcfFC3 = new WritableCellFormat(wfont);
            wcfFC3.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC3.setAlignment(Alignment.CENTRE);
            //wcfFC.setBackground(Colour.AQUA);
            Label label=new Label(0, 0, tmptitle, wcfFC3);

            WritableCellFormat cellFormat=new WritableCellFormat();
            cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
            cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            cellFormat.setWrap(true);
            //cellFormat.setBackground(Colour.AQUA);
            cellFormat.setFont(wfont);
            label.setCellFormat(cellFormat);
            wsheet.addCell(label);
            wsheet.addCell(new Label(0, 0, tmptitle, wcfFC3));
            wsheet.setRowView(0,800); //第一行高度
            wsheet.setRowView(1,660); //第一行高度
            wsheet.mergeCells(0, 0, 18, 0);  //合并单元格
            wsheet.setColumnView(0,5); //宽度
            wsheet.setColumnView(1,6); //宽度
            wsheet.setColumnView(2,5); //宽度
            wsheet.setColumnView(3,5); //宽度
            wsheet.setColumnView(4,5); //宽度
            wsheet.setColumnView(5,8); //宽度
            wsheet.setColumnView(6,8); //宽度
            wsheet.setColumnView(7,8); //宽度
            wsheet.setColumnView(8,8); //宽度
            wsheet.setColumnView(9,8); //宽度
            wsheet.setColumnView(10,8); //宽度
            wsheet.setColumnView(11,8); //宽度
            wsheet.setColumnView(12,8); //宽度
            wsheet.setColumnView(13,8); //宽度
            wsheet.setColumnView(14,8); //宽度
            wsheet.setColumnView(15,8); //宽度
            wsheet.setColumnView(16,8); //宽度
            wsheet.setColumnView(17,8); //宽度
            wsheet.setColumnView(18,8); //宽度

            // 开始生成主体内容
            wfont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 8,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);
            wcfFC.setWrap(true);

            wsheet.addCell(new Label(0, 1, "序号",wcfFC));
            wsheet.addCell(new Label(1, 1, "品种名",wcfFC));
            wsheet.addCell(new Label(2, 1, "品种序号",wcfFC));
            wsheet.addCell(new Label(3, 1, "品牌",wcfFC));
            wsheet.addCell(new Label(4, 1, "型号",wcfFC));
            wsheet.addCell(new Label(5, 1, "平台编码",wcfFC));
            wsheet.addCell(new Label(6, 1, "产品名称",wcfFC));
            wsheet.addCell(new Label(7, 1, "产品规格",wcfFC));
            wsheet.addCell(new Label(8, 1, "产品型号",wcfFC));
            wsheet.addCell(new Label(9, 1, "注册证号",wcfFC));
            wsheet.addCell(new Label(10, 1, "生产商（品牌）",wcfFC));
            wsheet.addCell(new Label(11, 1, "计价单位",wcfFC));
            wsheet.addCell(new Label(12, 1, "平台情况",wcfFC));
            wsheet.addCell(new Label(13, 1, "进口/国产",wcfFC));
            wsheet.addCell(new Label(14, 1, "平台单价（元）",wcfFC));
            wsheet.addCell(new Label(15, 1, "是否医保",wcfFC));
            wsheet.addCell(new Label(16, 1, "备注",wcfFC));
            wsheet.addCell(new Label(17, 1, "同意数／总票数",wcfFC));
            wsheet.addCell(new Label(18, 1, "预选结果",wcfFC));
            // 表头结束

            wfont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 8,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);// 添加边框
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);
            wcfFC.setWrap(true);


            WritableCellFormat wcfFCt = new WritableCellFormat(wfont);
            //设置背景颜色;
            wcfFCt.setBackground(Colour.GREEN);
            wcfFCt.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFCt.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFCt.setAlignment(Alignment.CENTRE);

            WritableCellFormat wcfFCb = new WritableCellFormat(wfont);
            //设置背景颜色;
            wcfFCb.setBackground(Colour.RED);
            wcfFCb.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFCb.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFCb.setAlignment(Alignment.CENTRE);

            int count=mmList.size();
            int mmpinzhongKaishihang=2;
            int mmpinpaiKaishihang=2;
            int mmxinghaoKaishihang=2;
            int a=1;

            for(int i=0;i<count;i++){
                a=a+1;
                Map<String,Object> mmPinzhongMap=mmList.get(i);

                String mmPinzhongOne = mmPinzhongMap.get("pinzhongxuhao") == null ? "" : mmPinzhongMap.get("pinzhongxuhao").toString();
                String mmPinzhongming = mmPinzhongMap.get("pinzhongming") == null ? "" : mmPinzhongMap.get("pinzhongming").toString();
                int mmHebingshu = 0;

                List<Map<String,Object>> mmPinpailist = ddYiyonghaocaiService.findPinpaiByPinzhong(ppxiangmuid,mmPinzhongOne);
                if (mmPinpailist.size()>0){
                    mmHebingshu = mmPinpailist.size()-1;
                }
                List<Map<String,Object>> mmPinpaiFenlei = ddService.getPinpaipaixuByPinzhong(ppxiangmuid,mmPinzhongOne);

                WritableCellFormat wcfFCpz = new WritableCellFormat(wfont);
                wcfFCpz.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
                wcfFCpz.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
                wcfFCpz.setAlignment(Alignment.CENTRE);

                if(mmPinpaiFenlei.size()>=4){
                    List<Map<String, Object>> mmAgreeNumList=ddService.getAgreeListByXiangMuIdAndPinZhong(ppxiangmuid,Integer.valueOf(mmPinzhongOne));
                    String mmpaoshu=mmAgreeNumList.get(2).get("agreenumber").toString();
                    int mmpaoshunumber=Integer.parseInt(mmpaoshu);
                    if(mmNumber>=mmpaoshunumber){
                        //设置背景颜色;
                        wcfFCpz.setBackground(Colour.RED);
                    }
                }
                if(mmPinpaiFenlei.size()==3){
                    List<Map<String, Object>> mmAgreeNumList=ddService.getAgreeListByXiangMuIdAndPinZhong(ppxiangmuid,Integer.valueOf(mmPinzhongOne));
                    String mmpaoshu=mmAgreeNumList.get(2).get("agreenumber").toString();
                    int mmpaoshunumber=Integer.parseInt(mmpaoshu);
                    if(mmNumber>=mmpaoshunumber){
                        //设置背景颜色;
                        wcfFCpz.setBackground(Colour.RED);
                    }
                }
                if(mmPinpaiFenlei.size()==2){
                    List<Map<String, Object>> mmAgreeNumList=ddService.getAgreeListByXiangMuIdAndPinZhong(ppxiangmuid,Integer.valueOf(mmPinzhongOne));
                    String mmpaoshu=mmAgreeNumList.get(1).get("agreenumber").toString();
                    int mmpaoshunumber=Integer.parseInt(mmpaoshu);
                    if(mmNumber>=mmpaoshunumber){
                        //设置背景颜色;
                        wcfFCpz.setBackground(Colour.RED);

                    }
                }
                if(mmPinpaiFenlei.size()==1){
                    List<Map<String, Object>> mmAgreeNumList=ddService.getAgreeListByXiangMuIdAndPinZhong(ppxiangmuid,Integer.valueOf(mmPinzhongOne));
                    String mmpaoshu=mmAgreeNumList.get(0).get("agreenumber").toString();
                    int mmpaoshunumber=Integer.parseInt(mmpaoshu);
                    if(mmNumber>=mmpaoshunumber){
                        //设置背景颜色;
                        wcfFCpz.setBackground(Colour.RED);
                    }
                }
                for (int j = 0; j < mmPinpaiFenlei.size(); j++) {
                    int s=0;
                    int mmPinpaiHebingshu = 0;
                    Map<String,Object> mmPinpaiMap = mmPinpaiFenlei.get(j);
                    if (mmPinpaiMap!=null){
                        String mmPinpaiOne = mmPinpaiMap.get("pinpaixuhao") == null ? "" : mmPinpaiMap.get("pinpaixuhao").toString();
                        List<Map<String,Object>> mmXinhaoList = ddYiyonghaocaiService.findXinghaoByPinpai(ppxiangmuid,mmPinzhongOne,mmPinpaiOne);
                        if (mmXinhaoList.size()>0){
                            mmPinpaiHebingshu = mmXinhaoList.size()-1;
                        }
                        for (int k = 0; k < mmXinhaoList.size(); k++) {
                            Map<String, Object> mmXinhaoMap = mmXinhaoList.get(k);
                            if (mmXinhaoMap != null) {
                                String mmXuhao = mmXinhaoMap.get("ordernumber")==null ? "" :mmXinhaoMap.get("ordernumber").toString();
                                String mmPinzhong=mmXinhaoMap.get("pinzhongxuhao")==null ? "" :mmXinhaoMap.get("pinzhongxuhao").toString();
                                String mmpinpai=mmXinhaoMap.get("pinpaixuhao")==null ? "" :mmXinhaoMap.get("pinpaixuhao").toString();
                                String mmxinghaochaifen=mmXinhaoMap.get("xinghaoxuhao")==null ? "" :mmXinhaoMap.get("xinghaoxuhao").toString();
                                String mmpingtaibianma=mmXinhaoMap.get("pingtaibianma")==null ? "" :mmXinhaoMap.get("pingtaibianma").toString();
                                String mmchanpinmingcheng=mmXinhaoMap.get("chanpinmingcheng")==null ? "" :mmXinhaoMap.get("chanpinmingcheng").toString();
                                String mmguige=mmXinhaoMap.get("chanpinguige")==null ? "" :mmXinhaoMap.get("chanpinguige").toString();
                                String mmxinghao=mmXinhaoMap.get("chanpinxinghao")==null ? "" :mmXinhaoMap.get("chanpinxinghao").toString();
                                String mmzhucezhenghao=mmXinhaoMap.get("zhucezhenghao")==null ? "" :mmXinhaoMap.get("zhucezhenghao").toString();
                                String mmshengchanshang=mmXinhaoMap.get("shengchanshangpinpai")==null ? "" :mmXinhaoMap.get("shengchanshangpinpai").toString();
                                String mmjijiadanwei=mmXinhaoMap.get("jijiadanwei")==null ? "" :mmXinhaoMap.get("jijiadanwei").toString();
                                String mmpingtaiqingkuang=mmXinhaoMap.get("pingtaiqingkuang")==null ? "" :mmXinhaoMap.get("pingtaiqingkuang").toString();
                                String mmjinkouguochan=mmXinhaoMap.get("jinkouguochan")==null ? "" :mmXinhaoMap.get("jinkouguochan").toString();
                                String mmpingtaidanjia=mmXinhaoMap.get("pingtaidanjia")==null ? "" :mmXinhaoMap.get("pingtaidanjia").toString();
                                String mmgongnengyongtu=mmXinhaoMap.get("gongnengyongtu")==null ? "" :mmXinhaoMap.get("gongnengyongtu").toString();
                                String mmshifouyibao=mmXinhaoMap.get("text10")==null ? "" :mmXinhaoMap.get("text10").toString();
                                String mmbeizhu=mmXinhaoMap.get("text11")==null ? "" :mmXinhaoMap.get("text11").toString();

                                List<Map<String, Object>> mmAgreeList=mmJieGuoList.stream()
                                        .filter(jieguo -> jieguo.get("xinghao").toString()
                                                .equals(ppxiangmuid) &&
                                                jieguo.get("pinzhong").toString()
                                                        .equals(mmPinzhong)&&
                                                jieguo.get("pinpai").toString().equals(mmpinpai)&&
                                                jieguo.get("result").toString().equals("1"))
                                        .collect(Collectors.toList());
                                String mmPiaoShu="";
                                String mmyijian="";
                                if(mmAgreeList==null){
                                    mmPiaoShu="0／"+String.valueOf(mmUserList.size());
                                    mmyijian="不通过";
                                }else {
                                    mmPiaoShu=String.valueOf(mmAgreeList.size())+"／"+String.valueOf(mmUserList.size());
                                    if(mmAgreeList.size()>mmNumber){
                                        mmyijian="通过";
                                    }else{
                                        mmyijian="不通过";
                                    }
                                }

                                wsheet.setRowView(mmxinghaoKaishihang,660); //第三行高度
                                wsheet.addCell(new Label(0, mmxinghaoKaishihang,mmXuhao,wcfFC));
                                wsheet.addCell(new Label(4, mmxinghaoKaishihang, mmxinghaochaifen,wcfFC));
                                wsheet.addCell(new Label(5, mmxinghaoKaishihang,mmpingtaibianma,wcfFC));
                                wsheet.addCell(new Label(6, mmxinghaoKaishihang,mmchanpinmingcheng,wcfFC));
                                wsheet.addCell(new Label(7, mmxinghaoKaishihang,mmguige,wcfFC));
                                wsheet.addCell(new Label(8, mmxinghaoKaishihang,mmxinghao,wcfFC));
                                wsheet.addCell(new Label(9, mmxinghaoKaishihang,mmzhucezhenghao,wcfFC));
                                wsheet.addCell(new Label(10, mmxinghaoKaishihang,mmshengchanshang,wcfFC));
                                wsheet.addCell(new Label(11, mmxinghaoKaishihang,mmjijiadanwei,wcfFC));
                                wsheet.addCell(new Label(12, mmxinghaoKaishihang,mmpingtaiqingkuang,wcfFC));
                                wsheet.addCell(new Label(13, mmxinghaoKaishihang,mmjinkouguochan,wcfFC));
                                wsheet.addCell(new Label(14, mmxinghaoKaishihang,mmpingtaidanjia,wcfFC));
                                wsheet.addCell(new Label(15, mmxinghaoKaishihang,mmshifouyibao,wcfFC));
                                wsheet.addCell(new Label(16, mmxinghaoKaishihang,mmbeizhu,wcfFC));

                                wsheet.addCell(new Label(17, mmxinghaoKaishihang,mmPiaoShu,wcfFC));
                                if(mmyijian=="通过"){
                                    wsheet.addCell(new Label(18, mmxinghaoKaishihang,mmyijian,wcfFCt));
                                }else {
                                    wsheet.addCell(new Label(18, mmxinghaoKaishihang,mmyijian,wcfFCb));
                                }

                                mmxinghaoKaishihang = mmxinghaoKaishihang+1;
                            }
                        }
                        wsheet.addCell(new Label(3, mmpinpaiKaishihang, mmPinpaiOne,wcfFC));
                        wsheet.mergeCells(3, mmpinpaiKaishihang, 3, mmpinpaiKaishihang+mmPinpaiHebingshu);
                        wsheet.mergeCells(17, mmpinpaiKaishihang, 17, mmpinpaiKaishihang+mmPinpaiHebingshu);
                        wsheet.mergeCells(18, mmpinpaiKaishihang, 18, mmpinpaiKaishihang+mmPinpaiHebingshu);
                        mmpinpaiKaishihang = mmpinpaiKaishihang+mmPinpaiHebingshu+1;
                    }
                }

                wsheet.addCell(new Label(1, mmpinzhongKaishihang, mmPinzhongming,wcfFCpz));
                wsheet.mergeCells(1, mmpinzhongKaishihang, 1, mmpinzhongKaishihang+mmHebingshu);
                wsheet.addCell(new Label(2, mmpinzhongKaishihang, mmPinzhongOne,wcfFCpz));
                wsheet.mergeCells(2, mmpinzhongKaishihang, 2, mmpinzhongKaishihang+mmHebingshu);
                mmpinzhongKaishihang = mmpinzhongKaishihang+mmHebingshu+1;

            }
            wbook.write(); // 写入文件
            wbook.close();


        }catch(Exception ex)
        {
            ex.printStackTrace();
        }finally{
            if(os!=null){
                try {
                    os.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

    }
    @RequestMapping("chongxintoupiaobiao")
    public void chongxintoupiaobiao(HttpServletRequest request, HttpServletResponse response,
                            @RequestParam("xiangmuid") String ppxiangmuid){

        response.reset();
        response.setContentType("application/x-download;charset=utf-8");
        List<Map<String,Object>> mmList = ddYiyonghaocaiService.findDaochuPinzhongAll(ppxiangmuid);
        List<Map<String,Object>> mmJieGuoList=ddService.findByxiangmuId(ppxiangmuid);
        List<Map<String, Object>> mmUserList=ddYltprenyuanfenpeiService.findRenyuanFenpeiByXiangmuId(ppxiangmuid);

        double mmNumber=Math.ceil((double) mmUserList.size()*((double)2/(double)3));
        int mmPassNumber=(int)mmNumber;

        List<Map<String, Object>> mmPinzhongList = new ArrayList<>();
        List<String> mmpinzhonglist=new ArrayList<>();
        Map map = new HashMap<String,Object>();
        for(int i=0;i<mmList.size();i++){
            Map<String,Object> mmPinzhongMap=mmList.get(i);
            String mmPinzhongOne = mmPinzhongMap.get("pinzhongxuhao") == null ? "" : mmPinzhongMap.get("pinzhongxuhao").toString();
            String mmPinzhongming = mmPinzhongMap.get("pinzhongming") == null ? "" : mmPinzhongMap.get("pinzhongming").toString();
            List<Map<String,Object>> mmPinpaiFenlei = ddYiyonghaocaiService.findPinpaiFenlei(ppxiangmuid,mmPinzhongOne);
            if(mmPinpaiFenlei.size()<=3){
                for (int j = 0; j < mmPinpaiFenlei.size(); j++) {
                    Map<String,Object> mmPinpaiMap = mmPinpaiFenlei.get(j);
                    if (mmPinpaiMap!=null) {
                        String mmPinpaiOne = mmPinpaiMap.get("pinpaixuhao") == null ? "" : mmPinpaiMap.get("pinpaixuhao").toString();
                        List<Map<String, Object>> mmAgreeList=mmJieGuoList.stream()
                                .filter(jieguo -> jieguo.get("xinghao").toString()
                                        .equals(ppxiangmuid) &&
                                        jieguo.get("pinzhong").toString()
                                                .equals(mmPinzhongOne)&&
                                        jieguo.get("pinpai").toString().equals(mmPinpaiOne)&&
                                        jieguo.get("result").toString().equals("1"))
                                .collect(Collectors.toList());
                        String mmPiaoShu="";
                        String mmyijian="";
                        if(mmAgreeList==null){
                            mmPiaoShu="0／"+String.valueOf(mmUserList.size());
                            mmyijian="不通过";
                            if(!mmpinzhonglist.contains(mmPinzhongOne)){
                                mmpinzhonglist.add(mmPinzhongOne);
                                map.put("pinzhongxuhao",mmPinzhongOne);
                                map.put("pinzhongming",mmPinzhongming);
                                mmPinzhongList.add(map);
                            }

                        }else {
                            mmPiaoShu=String.valueOf(mmAgreeList.size())+"／"+String.valueOf(mmUserList.size());
                            if(mmAgreeList.size()>=mmPassNumber){
                                mmyijian="通过";
                            }else{
                                mmyijian="不通过";
                                if(!mmpinzhonglist.contains(mmPinzhongOne)){
                                    mmpinzhonglist.add(mmPinzhongOne);
                                    map.put("pinzhongxuhao",mmPinzhongOne);
                                    map.put("pinzhongming",mmPinzhongming);
                                    mmPinzhongList.add(map);
                                }
                            }
                        }
                    }

                }
            }else{
                int a=0;
                for (int j = 0; j < mmPinpaiFenlei.size(); j++) {
                    Map<String,Object> mmPinpaiMap = mmPinpaiFenlei.get(j);
                    if (mmPinpaiMap!=null) {
                        String mmPinpaiOne = mmPinpaiMap.get("pinpaixuhao") == null ? "" : mmPinpaiMap.get("pinpaixuhao").toString();
                        List<Map<String, Object>> mmAgreeList=mmJieGuoList.stream()
                                .filter(jieguo -> jieguo.get("xinghao").toString()
                                        .equals(ppxiangmuid) &&
                                        jieguo.get("pinzhong").toString()
                                                .equals(mmPinzhongOne)&&
                                        jieguo.get("pinpai").toString().equals(mmPinpaiOne)&&
                                        jieguo.get("result").toString().equals("1"))
                                .collect(Collectors.toList());
                        String mmPiaoShu="";
                        String mmyijian="";
                        if(mmAgreeList==null){
                            mmPiaoShu="0／"+String.valueOf(mmUserList.size());
                            mmyijian="不通过";
                        }else {
                            mmPiaoShu=String.valueOf(mmAgreeList.size())+"／"+String.valueOf(mmUserList.size());
                            if(mmAgreeList.size()>=mmPassNumber){
                                mmyijian="通过";
                                a++;
                            }else{
                                mmyijian="不通过";
                            }
                        }
                    }
                }
                if(3>a){
                    map.put("pinzhongxuhao",mmPinzhongOne);
                    map.put("pinzhongming",mmPinzhongming);
                    mmPinzhongList.add(map);
                }
            }

        }




        OutputStream os=null;
        WritableWorkbook wbook=null;

        try{
            os = new BufferedOutputStream(response.getOutputStream());//得到输出流的语句
            //response.getOutputStream();// 取得输出流
            response.reset();// 清空输出流
            String wenjiantitle = "医用耗材目录表二次投票"+ StringHandle.tiemString(); //文件名
            String tmptitle = "医疗设备管理委员会医用耗材目录遴选表"; // 标题
            response.setHeader("Content-disposition", "attachment; filename="+new String(wenjiantitle.getBytes(),"iso8859-1")+".xls");// 设定输出文件头
            response.setContentType("application/vnd.ms-excel");// 定义输出类型
            wbook = Workbook.createWorkbook(os); // 建立excel文件
            WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称
            // 设置excel标题
            WritableFont wfont = new WritableFont(WritableFont.ARIAL, 14,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); //设置表格样式

            WritableCellFormat wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);

            WritableCellFormat wcfFC3 = new WritableCellFormat(wfont);
            wcfFC3.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC3.setAlignment(Alignment.CENTRE);
            //wcfFC.setBackground(Colour.AQUA);
            Label label=new Label(0, 0, tmptitle, wcfFC3);

            WritableCellFormat cellFormat=new WritableCellFormat();
            cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
            cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            cellFormat.setWrap(true);
            //cellFormat.setBackground(Colour.AQUA);
            cellFormat.setFont(wfont);
            label.setCellFormat(cellFormat);
            wsheet.addCell(label);
            wsheet.addCell(new Label(0, 0, tmptitle, wcfFC));
            wsheet.addCell(new Label(7, 0, tmptitle, wcfFC));
            wsheet.setRowView(0,500); //第一行高度
            wsheet.mergeCells(0, 0, 17, 0);  //合并单元格
            wsheet.setColumnView(0,5); //宽度
            wsheet.setColumnView(1,5); //序号宽度
            wsheet.setColumnView(2,5); //宽度
            wsheet.setColumnView(3,8); //宽度
            wsheet.setColumnView(4,8); //宽度
            wsheet.setColumnView(5,8); //宽度
            wsheet.setColumnView(6,8); //宽度
            wsheet.setColumnView(7,8); //宽度
            wsheet.setColumnView(8,8); //宽度
            wsheet.setColumnView(9,8); //宽度
            wsheet.setColumnView(10,8); //宽度
            wsheet.setColumnView(11,8); //宽度
            wsheet.setColumnView(12,8); //宽度
            wsheet.setColumnView(13,8); //宽度
            wsheet.setColumnView(14,8); //宽度
            wsheet.setColumnView(15,8); //宽度
            wsheet.setColumnView(16,8); //宽度
            wsheet.setColumnView(17,8); //宽度


            // 开始生成主体内容
            wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 8,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);
            wcfFC.setWrap(true);
           /* wsheet.addCell(new Label(0, 1, "排名",wcfFC));*/
            wsheet.addCell(new Label(0, 1, "序号",wcfFC));
            wsheet.addCell(new Label(1, 1, "耗材分类",wcfFC));
            wsheet.addCell(new Label(2, 1, "品种名",wcfFC));
            wsheet.addCell(new Label(3, 1, "品种序号",wcfFC));
            wsheet.addCell(new Label(4, 1, "品牌序号",wcfFC));
            wsheet.addCell(new Label(5, 1, "型号序号",wcfFC));
            wsheet.addCell(new Label(6, 1, "平台编码",wcfFC));
            wsheet.addCell(new Label(7, 1, "产品名称",wcfFC));
            wsheet.addCell(new Label(8, 1, "产品规格",wcfFC));
            wsheet.addCell(new Label(9, 1, "产品型号",wcfFC));
            wsheet.addCell(new Label(10, 1, "注册证号",wcfFC));
            wsheet.addCell(new Label(11, 1, "生产商（品牌）",wcfFC));
            wsheet.addCell(new Label(12, 1, "生产商",wcfFC));
            wsheet.addCell(new Label(13, 1, "计价单位",wcfFC));
            wsheet.addCell(new Label(14, 1, "平台情况",wcfFC));
            wsheet.addCell(new Label(15, 1, "进口/国产",wcfFC));
            wsheet.addCell(new Label(16, 1, "平台单价（元）",wcfFC));
            wsheet.addCell(new Label(17, 1, "简要功能用途",wcfFC));
            wsheet.addCell(new Label(18, 1, "备注1",wcfFC));
            wsheet.addCell(new Label(19, 1, "备注2",wcfFC));
            wsheet.addCell(new Label(20, 1, "备注3",wcfFC));
            wsheet.addCell(new Label(21, 1, "备注4",wcfFC));
            // 表头结束

            wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 8,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);
            wcfFC.setWrap(true);

            int count=mmPinzhongList.size();
            int mmpinzhongKaishihang=2;
            int mmpinpaiKaishihang=2;
            int mmxinghaoKaishihang=2;
            int a=1;

            for(int i=0;i<count;i++){
                a=a+1;
                Map<String,Object> mmPinzhongMap=mmPinzhongList.get(i);
                String mmPinzhongOne = mmPinzhongMap.get("pinzhongxuhao") == null ? "" : mmPinzhongMap.get("pinzhongxuhao").toString();
                String mmPinzhongming = mmPinzhongMap.get("pinzhongming") == null ? "" : mmPinzhongMap.get("pinzhongming").toString();
                int mmHebingshu = 0;

                List<Map<String,Object>> mmPinpailist = ddYiyonghaocaiService.findPinpaiByPinzhong(ppxiangmuid,mmPinzhongOne);
                if (mmPinpailist.size()>0){
                    mmHebingshu = mmPinpailist.size()-1;
                }
                List<Map<String,Object>> mmPinpaiFenlei = ddYiyonghaocaiService.findPinpaiFenlei(ppxiangmuid,mmPinzhongOne);
                for (int j = 0; j < mmPinpaiFenlei.size(); j++) {
                    int s=0;
                    int mmPinpaiHebingshu = 0;
                    Map<String,Object> mmPinpaiMap = mmPinpaiFenlei.get(j);
                    if (mmPinpaiMap!=null){
                        String mmPinpaiOne = mmPinpaiMap.get("pinpaixuhao") == null ? "" : mmPinpaiMap.get("pinpaixuhao").toString();
                        List<Map<String,Object>> mmXinhaoList = ddYiyonghaocaiService.findXinghaoByPinpai(ppxiangmuid,mmPinzhongOne,mmPinpaiOne);
                        if (mmXinhaoList.size()>0){
                            mmPinpaiHebingshu = mmXinhaoList.size()-1;
                        }
                        for (int k = 0; k < mmXinhaoList.size(); k++) {
                            Map<String, Object> mmXinhaoMap = mmXinhaoList.get(k);
                            if (mmXinhaoMap != null) {
                                String mmXuhao = mmXinhaoMap.get("ordernumber")==null ? "" :mmXinhaoMap.get("ordernumber").toString();
                                String mmHaocaifenlei = mmXinhaoMap.get("haocaifenlei")==null ? "" :mmXinhaoMap.get("haocaifenlei").toString();
                                String mmPinzhong=mmXinhaoMap.get("pinzhongxuhao")==null ? "" :mmXinhaoMap.get("pinzhongxuhao").toString();
                                String mmpinpai=mmXinhaoMap.get("pinpaixuhao")==null ? "" :mmXinhaoMap.get("pinpaixuhao").toString();
                                String mmxinghaochaifen=mmXinhaoMap.get("xinghaoxuhao")==null ? "" :mmXinhaoMap.get("xinghaoxuhao").toString();
                                String mmpingtaibianma=mmXinhaoMap.get("pingtaibianma")==null ? "" :mmXinhaoMap.get("pingtaibianma").toString();
                                String mmchanpinmingcheng=mmXinhaoMap.get("chanpinmingcheng")==null ? "" :mmXinhaoMap.get("chanpinmingcheng").toString();
                                String mmguige=mmXinhaoMap.get("chanpinguige")==null ? "" :mmXinhaoMap.get("chanpinguige").toString();
                                String mmxinghao=mmXinhaoMap.get("chanpinxinghao")==null ? "" :mmXinhaoMap.get("chanpinxinghao").toString();
                                String mmzhucezhenghao=mmXinhaoMap.get("zhucezhenghao")==null ? "" :mmXinhaoMap.get("zhucezhenghao").toString();
                                String mmshengchanshangpinpai=mmXinhaoMap.get("shengchanshangpinpai")==null ? "" :mmXinhaoMap.get("shengchanshangpinpai").toString();
                                String mmshengchanshang=mmXinhaoMap.get("shengchanshang")==null ? "" :mmXinhaoMap.get("shengchanshang").toString();
                                String mmjijiadanwei=mmXinhaoMap.get("jijiadanwei")==null ? "" :mmXinhaoMap.get("jijiadanwei").toString();
                                String mmpingtaiqingkuang=mmXinhaoMap.get("pingtaiqingkuang")==null ? "" :mmXinhaoMap.get("pingtaiqingkuang").toString();
                                String mmjinkouguochan=mmXinhaoMap.get("jinkouguochan")==null ? "" :mmXinhaoMap.get("jinkouguochan").toString();
                                String mmpingtaidanjia=mmXinhaoMap.get("pingtaidanjia")==null ? "" :mmXinhaoMap.get("pingtaidanjia").toString();
                                String mmgongnengyongtu=mmXinhaoMap.get("gongnengyongtu")==null ? "" :mmXinhaoMap.get("gongnengyongtu").toString();
                                String mmtext1=mmXinhaoMap.get("text1")==null ? "" :mmXinhaoMap.get("text1").toString();
                                String mmtext2=mmXinhaoMap.get("text2")==null ? "" :mmXinhaoMap.get("text2").toString();
                                String mmtext3=mmXinhaoMap.get("text3")==null ? "" :mmXinhaoMap.get("text3").toString();
                                String mmtext4=mmXinhaoMap.get("text4")==null ? "" :mmXinhaoMap.get("text4").toString();


                                wsheet.addCell(new Label(0, mmxinghaoKaishihang,mmXuhao,wcfFC));
                                wsheet.addCell(new Label(1, mmxinghaoKaishihang,mmHaocaifenlei,wcfFC));
                                wsheet.addCell(new Label(5, mmxinghaoKaishihang, mmxinghaochaifen,wcfFC));
                                wsheet.addCell(new Label(6, mmxinghaoKaishihang,mmpingtaibianma,wcfFC));
                                wsheet.addCell(new Label(7, mmxinghaoKaishihang,mmchanpinmingcheng,wcfFC));
                                wsheet.addCell(new Label(8, mmxinghaoKaishihang,mmguige,wcfFC));
                                wsheet.addCell(new Label(9, mmxinghaoKaishihang,mmxinghao,wcfFC));
                                wsheet.addCell(new Label(10, mmxinghaoKaishihang,mmzhucezhenghao,wcfFC));
                                wsheet.addCell(new Label(11, mmxinghaoKaishihang,mmshengchanshangpinpai,wcfFC));
                                wsheet.addCell(new Label(12, mmxinghaoKaishihang,mmshengchanshang,wcfFC));
                                wsheet.addCell(new Label(13, mmxinghaoKaishihang,mmjijiadanwei,wcfFC));
                                wsheet.addCell(new Label(14, mmxinghaoKaishihang,mmpingtaiqingkuang,wcfFC));
                                wsheet.addCell(new Label(15, mmxinghaoKaishihang,mmjinkouguochan,wcfFC));
                                wsheet.addCell(new Label(16, mmxinghaoKaishihang,mmpingtaidanjia,wcfFC));
                                wsheet.addCell(new Label(17, mmxinghaoKaishihang,mmgongnengyongtu,wcfFC));
                                wsheet.addCell(new Label(18, mmxinghaoKaishihang,mmtext1,wcfFC));
                                wsheet.addCell(new Label(19, mmxinghaoKaishihang,mmtext2,wcfFC));
                                wsheet.addCell(new Label(20, mmxinghaoKaishihang,mmtext3,wcfFC));
                                wsheet.addCell(new Label(21, mmxinghaoKaishihang,mmtext4,wcfFC));


                                mmxinghaoKaishihang = mmxinghaoKaishihang+1;
                            }
                        }
                        wsheet.addCell(new Label(4, mmpinpaiKaishihang, mmPinpaiOne,wcfFC));
                        wsheet.mergeCells(4, mmpinpaiKaishihang, 4, mmpinpaiKaishihang+mmPinpaiHebingshu);

                        mmpinpaiKaishihang = mmpinpaiKaishihang+mmPinpaiHebingshu+1;
                    }
                }
                wsheet.addCell(new Label(2, mmpinzhongKaishihang, mmPinzhongming,wcfFC));
                wsheet.mergeCells(2, mmpinzhongKaishihang, 2, mmpinzhongKaishihang+mmHebingshu);
                wsheet.addCell(new Label(3, mmpinzhongKaishihang, mmPinzhongOne,wcfFC));
                wsheet.mergeCells(3, mmpinzhongKaishihang, 3, mmpinzhongKaishihang+mmHebingshu);
                mmpinzhongKaishihang = mmpinzhongKaishihang+mmHebingshu+1;

            }
            wbook.write(); // 写入文件
            wbook.close();


        }catch(Exception ex)
        {
            ex.printStackTrace();
        }finally{
            if(os!=null){
                try {
                    os.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

    }

}
